package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.models.TSalesConsultantCheckModel;
import com.iris.live.services.data.models.用户Model;
import com.iris.live.services.data.repositories.SalesConsultantCheckRepositorySql;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.services.DataCheckService;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: wangzhenyan
 * 2017/5/22 15:30
 */
@Repository
public class SalesConsultantCheckRepositorySqlImpl extends BaseRepository implements SalesConsultantCheckRepositorySql {

    @Override
    public List<TSalesConsultantCheckModel> findSalesConsultantCheckByImportBatchAndUser(String importBatch, 用户Model user) {
        String sql = "SELECT a.* FROM t_sales_consultant_check a \n" +
                "WHERE a.import_batch = :importBatch\n" +
                "AND a.permit = :permit" ;
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",user.get许可());
        return this.getList(sql,params);
    }

    @Override
    public PageData getContrastResultOfSalesConsultant(RequestBase req, String importBatch, String permit) {
        String sql = "SELECT\n" +
                "\ta.id,\n" +
                "\ta.custom_tel AS customTel,   \n" +
                "\ta.sc,  \n" +
                "\ta.vin,  \n" +
                "\ta.intent_car AS intentCar,\n" +
                "\ta.sales_status AS salesStatus,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.io_dt END AS ioDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.order_cus_name END AS orderCusName,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.io_amount END AS ioAmount,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.contract_signed_dt END AS contractSignedDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.invoice_dt END AS invoiceDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.invoice_uname END AS invoiceUname,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.deliver_dt END AS deliverDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.price_ti END AS priceTi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_in END AS freebyIn,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_sub_int END AS freebySubInt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_a END AS freebyA,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_b END AS freebyB,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_si END AS freebySi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_bl END AS freebyBl,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_sh END AS freebySh,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_oi END AS freebyOi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_bo END AS freebyBo,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_in END AS freebyIsIn,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_sssc END AS freebyIsSssc,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_sd END AS freebyIsSd,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_oil END AS freebyIsOil,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_pre END AS freebyIsPre,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_ti END AS freebyIsTi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.pur_tax_si END AS purTaxSi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.bo_price_ti END AS boPriceTi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.ex_in_price_ti END AS exInPriceTi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.lic_fee END AS licFee,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.oth_fee END AS othFee,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.fin_fee END AS finFee,\n" +
                "CASE WHEN a.d_io_dt = 0 THEN '1' ELSE '0' END AS dIoDt,\n" +
                "CASE WHEN a.d_order_cus_name = 0 THEN '1' ELSE '0' END AS dOrderCusName,\n" +
                "CASE WHEN a.d_io_amount = 0 THEN '1' ELSE '0' END AS dIoAmount,\n" +
                "CASE WHEN a.d_contract_signed_dt = 0 THEN '1' ELSE '0' END AS dContractSignedDt,\n" +
                "CASE WHEN a.d_invoice_dt = 0 THEN '1' ELSE '0' END AS dInvoiceDt,\n" +
                "CASE WHEN a.d_invoice_uname = 0 THEN '1' ELSE '0' END AS dInvoiceUname,\n" +
                "CASE WHEN a.d_deliver_dt = 0 THEN '1' ELSE '0' END AS dDeliverDt,\n" +
                "CASE WHEN a.d_price_ti = 0 THEN '1' ELSE '0' END AS dPriceTi,\n" +
                "CASE WHEN a.d_freeby_in = 0 THEN '1' ELSE '0' END AS dFreebyIn,\n" +
                "CASE WHEN a.d_freeby_sub_int = 0 THEN '1' ELSE '0' END AS dFreebySubInt,\n" +
                "CASE WHEN a.d_freeby_a = 0 THEN '1' ELSE '0' END AS dFreebyA,\n" +
                "CASE WHEN a.d_freeby_b = 0 THEN '1' ELSE '0' END AS dFreebyB,\n" +
                "CASE WHEN a.d_freeby_si = 0 THEN '1' ELSE '0' END AS dFreebySi,\n" +
                "CASE WHEN a.d_freeby_bl = 0 THEN '1' ELSE '0' END AS dFreebyBl,\n" +
                "CASE WHEN a.d_freeby_sh = 0 THEN '1' ELSE '0' END AS dFreebySh,\n" +
                "CASE WHEN a.d_freeby_oi = 0 THEN '1' ELSE '0' END AS dFreebyOi,\n" +
                "CASE WHEN a.d_freeby_bo = 0 THEN '1' ELSE '0' END AS dFreebyBo,\n" +
                "CASE WHEN a.d_freeby_is_in = 0 THEN '1' ELSE '0' END AS dFreebyIsIn,\n" +
                "CASE WHEN a.d_freeby_is_sssc = 0 THEN '1' ELSE '0' END AS dFreebyIsSssc,\n" +
                "CASE WHEN a.d_freeby_is_sd = 0 THEN '1' ELSE '0' END AS dFreebyIsSd,\n" +
                "CASE WHEN a.d_freeby_is_oil = 0 THEN '1' ELSE '0' END AS dFreebyIsOil,\n" +
                "CASE WHEN a.d_freeby_is_pre = 0 THEN '1' ELSE '0' END AS dFreebyIsPre,\n" +
                "CASE WHEN a.d_freeby_is_ti = 0 THEN '1' ELSE '0' END AS dFreebyIsTi,\n" +
                "CASE WHEN a.d_pur_tax_si = 0 THEN '1' ELSE '0' END AS dPurTaxSi,\n" +
                "CASE WHEN a.d_bo_price_ti = 0 THEN '1' ELSE '0' END AS dBoPriceTi,\n" +
                "CASE WHEN a.d_ex_in_price_ti = 0 THEN '1' ELSE '0' END AS dExInPriceTi,\n" +
                "CASE WHEN a.d_lic_fee = 0 THEN '1' ELSE '0' END AS dLicFee,\n" +
                "CASE WHEN a.d_oth_fee = 0 THEN '1' ELSE '0' END AS dOthFee,\n" +
                "CASE WHEN a.d_fin_fee = 0 THEN '1' ELSE '0' END AS dFinFee,\n" +
                "\ta.compare_result AS compareResult,\n" +
                "\ta.total_score AS totalScore\n" +
                "FROM t_sales_consultant_check a\n" +
                "LEFT JOIN 用户 b ON a.import_user = b.`用户名`\n" +
                "WHERE a.import_batch = :importBatch\n" +
//                "AND b.许可 = :许可\n" ;
//                "AND b.角色 = :角色";
                "AND a.permit = :permit " +
                "AND a.compare_result <> '04'";
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",permit);
//        params.put("角色","销售顾问");
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }

    @Override
    public PageData getRankingOfSalesConsultant(DataCheckService.RankingRequest req) {
        String sql = "SELECT cast(@rowNum \\:= @rowNum + 1 AS DECIMAL(3,0))AS ranking, " +
                "  f.permit,   " +
                "  f.`经销商名` AS distributor,   " +
                "  f.CTA,   " +
                "  f.sc,   " +
                "  IFNULL(score,'0') AS score " +
                "FROM (   " +
                "  SELECT    " +
                "  @rowNum \\:= 0 AS ranking,   " +
                "  a.permit,   " +
                "  b.`经销商名`,   " +
                "  b.CTA,   " +
                "  a.sc,   " +
                "  cast(avg(a.total_score) AS DECIMAL(5,2)) score  " +
                "  FROM   " +
                "    (   " +
                "    SELECT * FROM t_sales_consultant_check WHERE cast(import_batch AS date) >= cast(:startBatch AS date) AND cast(import_batch as date) <= cast(:endBatch AS date)    " +
                "     ) a   " +
                "LEFT JOIN `lsh经销商表` b ON a.permit = b.`许可_BMBS`   " +
                "GROUP BY a.permit,a.sc   " +
                "HAVING b.CTA = (   " +
                "     SELECT c.CTA from `lsh经销商表` c WHERE c.`许可_BMBS` = :permit)            " +
                "ORDER BY AVG(a.total_score) DESC    " +
                ") f   ";

        Map<String,Object> params = new HashMap<>();
        params.put("permit",req.permit); //许可
        params.put("startBatch",req.startBatch); // 开始批次
        params.put("endBatch",req.endBatch); //结束批次
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }
}
